from sqlalchemy import create_engine, text, Column, Integer, String, Text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import declarative_base

# 为 SQLAlchemy 定义数据库 URL地址
SQLALCHEMY_DATABASE_URL = 'mysql+pymysql://root:xx123456@39.98.46.92:3309/new_fast'


engine = create_engine(SQLALCHEMY_DATABASE_URL)  # 配置
conn = engine.connect()   # 连上

# sqlalchemy 2.x 需用text(sql)
result = conn.execute(text("SELECT * from user where username='yoyo';"))
print(result)
# print(result.fetchone())
print(result.fetchall())

# conn.close()

DBBase = declarative_base()

# 创建学生表
class Student(DBBase):
    __tablename__ = 'students'  # 数据库表名
    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    address = Column(String(30))

    def __repr__(self):
        # 实例的时候显示具体名称内容
        return f'<id={self.id} name={self.name} ...>'

# 建表
DBBase.metadata.create_all(engine)  # 将模型映射到数据库中


# 新增数据
# 创建一个SessionLocal 数据库会话
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
session = SessionLocal()  # 实例化

# for i in range(100):
#     stu_obj = Student(name=f'xx_{i}', address='上海市')
#     session.add(stu_obj)
# session.commit()  # 提交


# 查询数据  ORM
res = session.query(Student)
print(res)
print(res.all())   # List[Obj]

# for obj in res.all():
#     print(obj.id, obj.name, obj.address)


# 序列化
from pydantic import BaseModel


class StudentOut(BaseModel):
    id: int
    name: str
    address: str

    class Config:
        orm_mode = True   # 1.x 版本
        # from_attributes = True  # 2.x版本


# for obj in res.all():
#     print(obj)
#     a = StudentOut.from_orm(obj)
#     print(a.dict())

# 条件查询  res = session.query(Student)
res2 = session.query(Student).filter(Student.name == 'xx').all()
print(res2)

# filter_by 功能少一些， 简写
res2 = session.query(Student).filter_by(name='xx').all()
print(res2)

# 删除
session.query(Student).filter(Student.name == 'xx_70').delete()
session.commit()  # 提交才能成功
# 更新
session.query(Student).filter(Student.name == 'xx').update({"address": "北京市"})
session.commit()  # 提交才能成功
